package com.bootcamp.s3_1013.database._1019_JBUtils.work;

import com.bootcamp.s3_1013.database._1019_JBUtils.City;
import com.bootcamp.s3_1013.database.utils.C3P0Utils;
import com.bootcamp.s3_1013.database.utils.DBCPUtils;
import com.bootcamp.s3_1013.database.utils.DruidUtils;
import com.bootcamp.s3_1013.database.utils.MyBeanListResultSetHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * @program: myClassCode
 * @description: use Druid/C3P0/DBCP pool to manupilate data
 * @author: Keyu Li
 * @create: 2021-10-19 20:02
 **/

public class Ex1_pool {
    public static void main(String[] args) {
        Connection connection = null;
        int choice = 3;
        switch (choice) {
            case 1:
                //from dbcp
                connection = DBCPUtils.getConnection();
                break;
            case 2:
                //from c3p0
                connection = C3P0Utils.getConnection();
                break;
            case 3:
                //from druid
                connection = DruidUtils.getConnection();
                break;
        }
        //执行增删查改
        manipulateDB(connection);
        //关闭资源
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    private static void manipulateDB(Connection connection) {
        //选择city表进行增删改查
        try {
            //开启事务
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            //显示全表
            ResultSet resultSetStart = statement.executeQuery("select * from city");
            List<City> cites = (List)new MyBeanListResultSetHandler().handle(resultSetStart);
            System.out.println("修改前：\n"+cites);
            //增加 7 郑州 41
            statement.executeUpdate("insert into city values(7,'郑州',41)");
            //删除 id为 1 项
            statement.executeUpdate("delete from city where id=1 ");
            //更改 长沙 为常德
            statement.executeUpdate("update city set name='常德' where name='长沙'");
            //显示全表
            ResultSet resultSetEnd = statement.executeQuery("select * from city");
            cites = (List) new MyBeanListResultSetHandler().handle(resultSetEnd);
            System.out.println("修改后：\n"+cites);
            //回滚
            connection.rollback();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
